﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;
using MYSQL_db.Model;

namespace MYSQL_db.DAL
{
    class MemberDAL
    {
        //把公共的代码封装到一个方法中，这样可以避免重复性的代码，提高代码复用性
        private static Member ToMember(DataRow row)
        {   //把从数据库中查询到的行中字段值，封装到对象中
            Member mem = new Member();
            mem.Id = (long)row["Id"];
            mem.MemCardID = (string)row["MemCardID"];
            mem.Name = (string)SqlHelper.FromDbValue(row["Name"]);
            mem.Tel = (string)SqlHelper.FromDbValue(row["Tel"]);
            mem.IdentityCard = (string)SqlHelper.FromDbValue(row["IdentityCard"]);
            mem.Balance = (float)row["Balance"];
            mem.Date = (DateTime)row["Date"];
            mem.Status = (int)row["Status"];
            mem.ISN = (string)row["ISN"];
            //mem.comment = (string)row["comment"];
            return mem;
        }
        //无参数查询数据库，返回Member对象数组
        public static Member[] GetAll()
        {
            DataTable table = SqlHelper.ExecuteDataTable("SELECT A.Id,A.MemCardID,B.Name,B.Tel,A.Balance,B.IdentityCard,A.Date,A.ISN,A.Status  FROM t_card A,t_customer B WHERE A.MemCardID=B.MemCardID");
            Member[] mem = new Member[table.Rows.Count];
            for (int i = 0; i < table.Rows.Count; i++)
            {
                DataRow row = table.Rows[i]; //循环取出表中每一行
                mem[i] = ToMember(row); //把取到的每一行加入对象数组
                if (mem[i].Status == 0)
                {
                    mem[i].comment = "可用";
                }
                else
                {
                    mem[i].comment = "冻结";
                }
            }
            return mem; //返回对象数组
        }

        public static Member[] Search(string sql, List<MySqlParameter> parameters)
        {
            DataTable table =
                SqlHelper.ExecuteDataTable(sql, parameters.ToArray());

            Member[] mem = new Member[table.Rows.Count];
            for (int i = 0; i < table.Rows.Count; i++)
            {
                mem[i] = ToMember(table.Rows[i]);
            }
                return mem;
        }


    }
}
